-- 目前测试windows版本可用，linux不能使用SqlServer正则 替换数据 varchar处理中文会乱码
IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL
 DROP FUNCTION dbo.RegexReplace
GO
CREATE function dbo.regexReplace 
( 
@source ntext, --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 1, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
) 
returnS nvarchar(4000) AS 
begin 
declare @hr integer 
declare @objRegExp integer 
declare @result nvarchar(4000)

exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT 
IF @hr <> 0 begin 
exec @hr = sp_OADestroy @objRegExp 
return null 
end 
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp 
IF @hr <> 0 begin 
exec @hr = sp_OADestroy @objRegExp 
return null 
end 
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace 
IF @hr <> 0 begin 
exec @hr = sp_OADestroy @objRegExp 
return null 
end 
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase 
IF @hr <> 0 begin 
exec @hr = sp_OADestroy @objRegExp 
return null 
end 
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace 
IF @hr <> 0 begin 
exec @hr = sp_OADestroy @objRegExp 
return null 
end 
exec @hr = sp_OADestroy @objRegExp 
IF @hr <> 0 begin 
return null 
end 

return @result
end
GO

--开启 Ole Automation Procedures

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO

--关闭 Ole Automation Procedures

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'Ole Automation Procedures';

GO

--关闭高级选项

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

-- Example1：忽略大小写并替换
select dbo.regexReplace('<A HREF="www.jileiba.com" target="_blank" style="color:red;">123456</a>','<a[^>]*>[^<]*</a>','',1,1)
declare @html nvarchar(4000)='<p>
    Also Available - <a style="text-decoration: none" href="/isbn/9780199218691"><font
        color="#000FF"><b>Smith & Hogan: Criminal Law Cases & Materials 10th ed</b></font></a>
    <p>
        There is, as ever, detailed analysis of the many recent case developments, in particular,
        a revision of the chapter dealing with secondary liability and joint enterprise.</p>
</p>'
-- Example2: 使用贪婪匹配
select dbo.regexReplace(@html,'<a[^>]*>(.|\n)*?</a>','',1,1)
-- Example3：去除html标签
select dbo.regexReplace('<p><b>Key Contact:</b><br> Mr  Jack,  Zhou<br> General Manager<br> <p> Mr  Adu,  Ho<br> Marketing Director<br> Overseas Sales<br> <p> Ms  Winny,  Luo<br> Sales Manager<br> Overseas Sales<br> <p>'
,'<[^>]*>','',1,0) 
-- Example4：数据库字段值替换
update Books 
set [Description] = dbo.regexReplace([Description],'<a[^>]*>(.|\n)*?</a>','',1,1)

SELECT itm_id,itm_threecard_ind,itm_three_card_type,itm_xml FROM aeItem WHERE itm_id=1490

UPDATE aeItem set itm_threecard_ind=2,itm_three_card_type=2 WHERE itm_id=1490
-- 三卡课程/三卡类型初始化，更新已有field117和field118的课程
UPDATE aeItem set itm_xml= dbo.regexReplace(itm_xml,'<field117>.*</field117><field118>.*</field118>','<field117><field117 id="'+CONVERT(nvarchar(10),itm_threecard_ind)+'">'+CONVERT(nvarchar(10),itm_threecard_ind)+'</field117></field117>'+'<field118><field118 id="'+CONVERT(nvarchar(10),itm_three_card_type)+'">'+CONVERT(nvarchar(10),itm_three_card_type)+'</field118></field118>',1,0) WHERE CHARINDEX('field117',cast(itm_xml AS nvarchar(4000))) > 0;
-- 更新没有field117和field118的课程,添加到field27后边
UPDATE aeItem set itm_xml= dbo.regexReplace(itm_xml,'</field27></field27>','</field27></field27><field117><field117 id="'+CONVERT(nvarchar(10),itm_threecard_ind)+'">'+CONVERT(nvarchar(10),itm_threecard_ind)+'</field117></field117>'+'<field118><field118 id="'+CONVERT(nvarchar(10),itm_three_card_type)+'">'+CONVERT(nvarchar(10),itm_three_card_type)+'</field118></field118>',1,0) WHERE CHARINDEX('field117',cast(itm_xml AS nvarchar(4000))) <= 0;
